<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="JAeIl" id="JAeIl"><span data-lake-id="u0797d313" id="u0797d313">典型回答</span></h1>
  <p data-lake-id="u61fbeda9" id="u61fbeda9"><br></p>
  <p data-lake-id="u331d592f" id="u331d592f"><br></p>
  <p data-lake-id="uca261cb2" id="uca261cb2"><span data-lake-id="ue45a7451" id="ue45a7451">上一篇中介绍了POI的内存溢出以及几种Workbook，那么，我们在做文件写入的时候，该如何选择呢？他们的在内存使用上有啥差异呢？</span></p>
  <p data-lake-id="ud5e23550" id="ud5e23550"><span data-lake-id="ub5cbd26e" id="ub5cbd26e">​</span><br></p>
  <p data-lake-id="uafa76c65" id="uafa76c65"><span data-lake-id="u466bcb07" id="u466bcb07">我们接下来分别使用XSSFWorkbook和SXSSFWorkbook来写入一个Excel文件，分别看一下堆内存的使用情况。</span></p>
  <p data-lake-id="u14f75678" id="u14f75678"><br></p>
  <h3 data-lake-id="voSFF" id="voSFF"><span data-lake-id="u9d8135ff" id="u9d8135ff">使用XSSF写入文件</span></h3>
  <p data-lake-id="u4bc1ae9b" id="u4bc1ae9b"><br></p>
  <pre lang="java"><code>
package excel.write;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.UUID;

public class XSSFExcelTest {

    public static void main(String[] args) throws InterruptedException {
        // 创建一个新的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 创建一个新的表格
        Sheet sheet = workbook.createSheet("Example Sheet");
        for (int i = 0; i &lt; 10000; i++) {
            // 创建行（从0开始计数）
            Row row = sheet.createRow(i);
            for (int j = 0; j &lt; 100; j++) {
                // 在行中创建单元格（从0开始计数）
                Cell cell = row.createCell(j);

                // 设置单元格的值
                cell.setCellValue(UUID.randomUUID().toString());
            }
        }

        // 设置文件路径和名称
        String filename = "example.xlsx";

        try (FileOutputStream outputStream = new FileOutputStream(filename)) {
            // 将工作簿写入文件
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭工作簿资源
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
</code></pre>
  <p data-lake-id="uba917c8d" id="uba917c8d"><span data-lake-id="u2ca1263f" id="u2ca1263f"></span></p>
  <p data-lake-id="uce4fcee2" id="uce4fcee2"><span data-lake-id="uf1b3de28" id="uf1b3de28">​</span><br></p>
  <p data-lake-id="u88e0ddcb" id="u88e0ddcb"><span data-lake-id="u17eeec4c" id="u17eeec4c">运行main方法的过程中，通过arthas看一下堆内存的使用情况：</span></p>
  <p data-lake-id="u6fc83486" id="u6fc83486"><span data-lake-id="u951bb318" id="u951bb318">​</span><br></p>
  <pre lang="java"><code>
curl -O https://arthas.aliyun.com/arthas-boot.jar

java -jar arthas-boot.jar
</code></pre>
  <p data-lake-id="ud95537b8" id="ud95537b8"><br></p>
  <pre lang="java"><code>
➜  java -jar arthas-boot.jar
[INFO] JAVA_HOME: /Library/Java/JavaVirtualMachines/jdk1.8.0_311.jdk/Contents/Home/jre
[INFO] arthas-boot version: 3.7.1
[INFO] Found existing java process, please choose one and input the serial number of the process, eg : 1. Then hit ENTER.
* [1]: 41417 org.jetbrains.idea.maven.server.RemoteMavenServer36
  [2]: 4874 
  [3]: 43484 org.jetbrains.jps.cmdline.Launcher
  [4]: 43485 excel.write.XSSFExcelTest
4
[INFO] arthas home: /Users/hollis/.arthas/lib/3.7.1/arthas
[INFO] Try to attach process 43485
Picked up JAVA_TOOL_OPTIONS: 
[INFO] Attach process 43485 success.
[INFO] arthas-client connect 127.0.0.1 3658

</code></pre>
  <p data-lake-id="u23683d1b" id="u23683d1b"><br></p>
  <p data-lake-id="u9f50a9fa" id="u9f50a9fa"><span data-lake-id="ub6cbbaac" id="ub6cbbaac">执行memory命令（这个执行的时间点很重要，我是在</span><code data-lake-id="u33b410e7" id="u33b410e7"><span data-lake-id="u83c9a480" id="u83c9a480">String filename = "example.xlsx";</span></code><span data-lake-id="u7c85dc53" id="u7c85dc53">前输出了一行日志，然后sleep 50s，我在控制台看到这行日志之后开始查看堆内存情况）：</span></p>
  <p data-lake-id="u972a59ca" id="u972a59ca"><span data-lake-id="ubd47edd9" id="ubd47edd9">​</span><br></p>
  <pre lang="java"><code>
[arthas@43485]$ memory
</code></pre>
  <p data-lake-id="u2f558a7a" id="u2f558a7a"><br></p>
  <p data-lake-id="ub9e9d047" id="ub9e9d047"><span data-lake-id="uce469db2" id="uce469db2">得到结果：</span></p>
  <p data-lake-id="u451e3e02" id="u451e3e02"><span data-lake-id="u197f87f5" id="u197f87f5">​</span><br></p>
  <p data-lake-id="u0a9554c5" id="u0a9554c5"><img src="https://cdn.nlark.com/yuque/0/2023/png/5378072/1700375843532-30efbb19-7289-40a0-9628-452fecd98c9c.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_63%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="uc261a914" id="uc261a914"><br></p>
  <p data-lake-id="u1897ed65" id="u1897ed65"><span data-lake-id="u894053bb" id="u894053bb">即占用堆内存1200+M。</span></p>
  <p data-lake-id="ub2493390" id="ub2493390"><span data-lake-id="u0fd19a71" id="u0fd19a71">​</span><br></p>
  <p data-lake-id="u1fcb3b31" id="u1fcb3b31"><span data-lake-id="ue383ae5b" id="ue383ae5b">​</span><br></p>
  <h3 data-lake-id="Sf4XN" id="Sf4XN"><span data-lake-id="u668e8715" id="u668e8715">使用SXSSFWorkbook写入文件</span></h3>
  <p data-lake-id="uab405aa6" id="uab405aa6"><br></p>
  <pre lang="java"><code>
package excel.write;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.UUID;

public class SXSSFExcelTest {

    public static void main(String[] args) {
        // 创建一个新的工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();

        // 创建一个新的表格
        Sheet sheet = workbook.createSheet("Example Sheet");
        for(int i=0;i&lt;10000;i++){
            // 创建行（从0开始计数）
            Row row = sheet.createRow(i);
            for(int j=0;j&lt;100;j++){
                // 在行中创建单元格（从0开始计数）
                Cell cell = row.createCell(j);

                // 设置单元格的值
                cell.setCellValue(UUID.randomUUID().toString());
            }
        }

        // 设置文件路径和名称
        String filename = "example.xlsx";

        try (FileOutputStream outputStream = new FileOutputStream(filename)) {
            // 将工作簿写入文件
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭工作簿资源
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

</code></pre>
  <p data-lake-id="u2ff9520e" id="u2ff9520e"><span data-lake-id="u267d242a" id="u267d242a">​</span><br></p>
  <p data-lake-id="u4082eb85" id="u4082eb85"><span data-lake-id="u1ef923b6" id="u1ef923b6">同样通过Arthas查看内存占用情况：</span></p>
  <p data-lake-id="uf65cdc3e" id="uf65cdc3e"><span data-lake-id="u85fdcd93" id="u85fdcd93">​</span><br></p>
  <p data-lake-id="ub2f2f841" id="ub2f2f841"><img src="https://cdn.nlark.com/yuque/0/2023/png/5378072/1700375986735-35d470a5-c849-4201-ba99-cfa5175c8fbe.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_65%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="u9ce03136" id="u9ce03136"><br></p>
  <p data-lake-id="uf83a69c7" id="uf83a69c7"><span data-lake-id="uf5ad7b87" id="uf5ad7b87">占用内存在148M左右。</span></p>
  <h3 data-lake-id="n7IJt" id="n7IJt"><span data-lake-id="u63548e8a" id="u63548e8a">对比结果</span></h3>
  <p data-lake-id="u4bd19ebd" id="u4bd19ebd"><br></p>
  <p data-lake-id="ua255ac80" id="ua255ac80"><span data-lake-id="u2a47077d" id="u2a47077d">同样的一份文件写入，XSSFWorkbook需要1200+M，SXSSFWorkbook只需要148M。所以大文件的写入，使用SXSSFWorkbook是可以更加节省内存的。</span></p>
  <p data-lake-id="uc7b7fd16" id="uc7b7fd16"><span data-lake-id="ud0bc9f32" id="ud0bc9f32">​</span><br></p>
  <p data-lake-id="u75dcb144" id="u75dcb144"><span data-lake-id="u39ffc90b" id="u39ffc90b">如果不方便使用arthas，也可以直接在JVM启动参数中增加Xmx150m的参数，运行以上两段代码，使用XSSFWorkbook的会抛出OOM：</span></p>
  <p data-lake-id="u0db35536" id="u0db35536"><span data-lake-id="u2b319537" id="u2b319537">​</span><br></p>
  <p data-lake-id="u1b9b8d89" id="u1b9b8d89"><img src="https://cdn.nlark.com/yuque/0/2023/png/5378072/1700376378592-bfdf6705-3830-43e6-a967-b324da7467f3.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_26%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="uc4e698ac" id="uc4e698ac"><span data-lake-id="u0ffdfbfb" id="u0ffdfbfb">​</span><br></p>
  <p data-lake-id="u45579a75" id="u45579a75"><span data-lake-id="u81c79b11" id="u81c79b11">而使用SXSSFWorkbook时则不会。</span></p>
  <p data-lake-id="u83d25268" id="u83d25268"><span data-lake-id="uda64144a" id="uda64144a">​</span><br></p>
  <p data-lake-id="uc39213c4" id="uc39213c4"><span data-lake-id="uc4bb195d" id="uc4bb195d">所以，在使用POI时，如果要做大文件的写入，建议使用SXSSFWorkbook，会更加节省内存。</span></p>
  <p data-lake-id="u107eecb0" id="u107eecb0"><span data-lake-id="u412c97ba" id="u412c97ba">​</span><br></p>
  <h1 data-lake-id="m7kit" id="m7kit"><span data-lake-id="u6bf03201" id="u6bf03201">扩展知识</span></h1>
  <p data-lake-id="u8cb2d31a" id="u8cb2d31a"><br></p>
  <h2 data-lake-id="t11aq" id="t11aq"><span data-lake-id="ud1abfb53" id="ud1abfb53">为啥SXSSFWorkbook占用内存更小?</span></h2>
  <p data-lake-id="u16e90aa3" id="u16e90aa3"><br></p>
  <p data-lake-id="uddd7b2e1" id="uddd7b2e1"><br></p>
  <p data-lake-id="ufb11c74d" id="ufb11c74d"><br></p>
 </body>
</html>